--Sebastian y Pedro, Proyecto Final 2013
use master
go
if exists(select*from SysDataBases where name = 'dbSmartHouse')
begin
	drop database dbSmartHouse
end
go
create database dbSmartHouse
on(
	name=dbSmartHouse,
	filename='C:\dbSmartHouse.mdf'
)
go
use dbSmartHouse
go
-------- TABLAS --------
create table Roles(
	Idr int not null Primary Key,
	Nombre varchar(20) not null,
	EliminacionLogica bit not null
)
go
create table Usuarios(
	CI int not null Primary Key,
	Nombre varchar(20) not null,
	Apellido varchar(20) not null,
	Usuario varchar(20) unique not null,
	Contrasena varbinary(500) unique not null,
	Idr int,--Rol
	Foreign Key(Idr) References Roles(Idr),
	EliminacionLogica bit not null
)
go
create table Casas(
	Idc int Identity(1,1) not null Primary Key,
	Direccion varchar(100) not null,
	Foto varchar(200) not null,
	Servicio varchar(200) not null,
	EliminacionLogica bit not null
)
go
create table UsuarioXCasa(
	CI int not null,
	Idc int not null,	
	EliminacionLogica bit not null,
	primary key(CI,Idc),
	Foreign Key(CI) References Usuarios(CI),
	Foreign Key(Idc) References Casas(Idc)
)
go
create table Planos(
	Idp int Identity(1,1) not null Primary Key,
	Foto varchar(200) not null,
	Idc int not null,
	Foreign Key(Idc) References Casas(Idc),
	EliminacionLogica bit not null
)
go
create table Sensores(
	Id int not null,
	Estado bit not null,
	EjeX decimal not null,
	EjeY decimal not null,
	Idp int,
	EliminacionLogica bit not null,
	Foreign Key(Idp) References Planos(Idp),
    Primary Key (Idp, Id)
)
go
create table Luces(
	Id int not null,
	Estado bit not null,
	EjeX decimal not null,
	EjeY decimal not null,
	Idp int,
	EliminacionLogica bit not null,
	Foreign Key(Idp) References Planos(Idp),
	Primary Key (Idp, Id)
)
go
create table Camaras(
	DirRtsp varchar(200) not null,
	Estado bit not null,
	EjeX decimal not null,
	EjeY decimal not null,
	Idp int,
	EliminacionLogica bit not null,
	Foreign Key(Idp) References Planos(Idp),
	Primary Key (Idp, DirRtsp)
)
go
CREATE FUNCTION fnEscribeClave 
(
    @clave VARCHAR(25)
)
RETURNS VarBinary(8000)
AS
BEGIN
    
    
    DECLARE @pass AS VarBinary(8000)
    ------------------------------------
    ------------------------------------
    SET @pass = ENCRYPTBYPASSPHRASE('dbCurso09',@clave)--dbCurso09 es la llave para cifrar el campo.
    ------------------------------------
    ------------------------------------    
    RETURN @pass

END
go
CREATE FUNCTION fnLeeClave 
(
    @clave VARBINARY(8000)
)
RETURNS VARCHAR(25)
AS
BEGIN
    
    
    DECLARE @pass AS VARCHAR(25)
    ------------------------------------
    ------------------------------------
    --Se descifra el campo aplicandole la misma llave con la que se cifro dbCurso09
    SET @pass = DECRYPTBYPASSPHRASE('dbCurso09',@clave)
    ------------------------------------
    ------------------------------------    
    RETURN @pass

END

---------------------------------------------------------------------------------------
-- login para IIS
---------------------------------------------------------------------------------------

create user [IIS APPPOOL\DefaultAppPool] for login [IIS APPPOOL\DefaultAppPool]
go
 alter authorization on schema::[db_accessadmin] to [IIS APPPOOL\DefaultAppPool]
 alter authorization on schema::[db_securityadmin] to [IIS APPPOOL\DefaultAppPool]
 alter authorization on schema::[db_owner] to [IIS APPPOOL\DefaultAppPool]
 alter authorization on schema::[db_backupoperator] to [IIS APPPOOL\DefaultAppPool]
 alter authorization on schema::[db_ddladmin] to [IIS APPPOOL\DefaultAppPool]
 alter authorization on schema::[db_denydatawriter] to [IIS APPPOOL\DefaultAppPool]
 alter authorization on schema::[db_datareader] to [IIS APPPOOL\DefaultAppPool]
 alter authorization on schema::[db_datawriter] to [IIS APPPOOL\DefaultAppPool]
 alter authorization on schema::[db_denydatareader] to [IIS APPPOOL\DefaultAppPool]
 exec sp_addrolemember N'db_owner', N'IIS APPPOOL\DefaultAppPool'
 exec sp_addrolemember N'db_securityadmin', N'IIS APPPOOL\DefaultAppPool'

 go
---------------------------------------------------------
insert into Casas(Direccion,Foto,Servicio,EliminacionLogica) 
values('yi 1436','C:\Smart_House_System\Smart_House_System\Imagenes\Casas\casa1.jpg','ServicioUno',1)
insert into Casas(Direccion,Foto,Servicio,EliminacionLogica) 
values('alla 22','C:\Smart_House_System\Smart_House_System\Imagenes\Casas\casa2.jpg','ServicioDos',1)
go
insert into Roles(Idr,Nombre,EliminacionLogica) values(1,'Administrador',1)
insert into Roles(Idr,Nombre,EliminacionLogica) values(2,'Habitante Principal',1)
insert into Roles(Idr,Nombre,EliminacionLogica) values(3,'Habitante Secundario',1)
insert into Roles(Idr,Nombre,EliminacionLogica) values(4,'Habitante Terciario',1)
go
insert into Usuarios(CI,Nombre,Apellido,Usuario,Contrasena,Idr,EliminacionLogica) values(11111111,'Juan','Admin','admin',dbo.fnEscribeClave('11111111'),1,1)
insert into Usuarios(CI,Nombre,Apellido,Usuario,Contrasena,Idr,EliminacionLogica) values(22222222,'Habi','Principal','hab',dbo.fnEscribeClave('22222222'),2,1)

insert into Usuarios(CI,Nombre,Apellido,Usuario,Contrasena,Idr,EliminacionLogica) values(33333333,'Name3','Pacheco','user3',dbo.fnEscribeClave('33333333'),2,1)
insert into Usuarios(CI,Nombre,Apellido,Usuario,Contrasena,Idr,EliminacionLogica) values(44444444,'Name4','Rodriguez','user4',dbo.fnEscribeClave('44444444'),2,1)
insert into Usuarios(CI,Nombre,Apellido,Usuario,Contrasena,Idr,EliminacionLogica) values(55555555,'Name5','Castillo','user5',dbo.fnEscribeClave('55555555'),2,1)
insert into Usuarios(CI,Nombre,Apellido,Usuario,Contrasena,Idr,EliminacionLogica) values(66666666,'Name6','Zalayeta','user6',dbo.fnEscribeClave('66666666'),3,1)
insert into Usuarios(CI,Nombre,Apellido,Usuario,Contrasena,Idr,EliminacionLogica) values(77777777,'Name7','Forlan','user7',dbo.fnEscribeClave('77777777'),3,1)
insert into Usuarios(CI,Nombre,Apellido,Usuario,Contrasena,Idr,EliminacionLogica) values(88888888,'Name8','Lolo','user8',dbo.fnEscribeClave('88888888'),3,1)
go
insert into UsuarioXCasa(Idc,CI,EliminacionLogica) values(1,22222222,1)
insert into UsuarioXCasa(Idc,CI,EliminacionLogica) values(1,33333333,1)
insert into UsuarioXCasa(Idc,CI,EliminacionLogica) values(1,66666666,1)
insert into UsuarioXCasa(Idc,CI,EliminacionLogica) values(2,77777777,1)
insert into UsuarioXCasa(Idc,CI,EliminacionLogica) values(2,55555555,1)
insert into UsuarioXCasa(Idc,CI,EliminacionLogica) values(2,88888888,1)
insert into UsuarioXCasa(Idc,CI,EliminacionLogica) values(2,44444444,1)

go
insert into Planos(Foto,Idc,EliminacionLogica) values('C:\Smart_House_System\Smart_House_System\Imagenes\Planos\plano1.jpg',1,1)
--insert into Planos(Foto,Idc,EliminacionLogica) values('C:\Smart_House_System\Smart_House_System\Imagenes\Planos\plano2.jpg',2,1)